/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.diamondsys.embrapa;

import java.util.Date;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

/**
 *
 * @author Pablo
 */
@Stateless
public class SacFacade extends AbstractFacade<Sac> {
    @PersistenceContext(unitName = "com.diamondsys_embrapa_war_1.0-SNAPSHOTPU")
    private EntityManager em;

    /**
     *
     * @return
     */
    @Override
    public EntityManager getEntityManager() {
        return em;
    }

    /**
     *
     */
    public SacFacade() {
        super(Sac.class);
    }
    
    public List<Sac> findByClienteId(TbInst clienteId) {
        
        return em.createNativeQuery("SELECT MIN(s.id) AS id, MIN(s.data_Cadastro) AS data_Cadastro, s.assunto, s.tb_Inst_Cod_Instituicao " +
                              "FROM `nco`.`sac` s " +
                              "WHERE s.tb_Inst_Cod_Instituicao = :tbInstCodInstituicao GROUP BY s.assunto, s.tb_Inst_Cod_Instituicao, s.data_Cadastro", Sac.class)
                .setParameter("tbInstCodInstituicao", clienteId)
                .getResultList();
    }    
    
    public List<Sac> findByClienteIdAndAssunto(TbInst clienteId, String assunto, Date dataCadastro) {
        
        return em.createQuery("SELECT s FROM Sac s"
                            + " WHERE s.tbInstCodInstituicao = :tbInstCodInstituicao"
                            + "   AND s.assunto = :assunto"
                            + "   AND s.dataCadastro = :dataCadastro", Sac.class)
                .setParameter("tbInstCodInstituicao", clienteId)
                .setParameter("assunto", assunto)
                .setParameter("dataCadastro", dataCadastro)
                .getResultList();
    }  
    
    public int deletarPorID(int sacId) {
        return em.createNativeQuery(   "  DELETE FROM `nco`.`sac`"
                                     + "   WHERE `id` = :sacId")
                .setParameter("sacId", sacId)
                .executeUpdate();
    }    
    
}
